<?php
class M_enrollment_transactions extends MY_Model {
	
	private $__table = 'enrollment_transactions';
	
	// Create
	public function insert_enrollment_transaction($input)
	{
		$input['created_at'] = NOW;
		$this->db->insert($this->__table,$input);
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	//Retrieve
	public function get_enrollment_transaction($e_id)
	{
		$sql = "SELECT assesment, stat, requirements_for_admission, star_program, interview, requirements_checked, requirements_checked_remarks, payment FROM enrollment_transactions
				WHERE e_id = ?";
		$query = $this->db->query($sql, array($e_id));
		return $query->num_rows() > 0 ? $query->row() : FALSE;
	}
	
	//Update
	public function update_enrollment_transaction($input,$e_id)
	{
		$input['updated_at'] = NOW;
		$this->db->set($input)->where('e_id',$e_id)->update($this->__table);
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	public function get_all_sections($id=FALSE)
	{
		$sql = "SELECT sec.id as section_id,
					   sec.section_name,
					   lvl.level_desc,
					   sec.grade_level_id,
					   sec.adviser_id,
					   sec.max_load
				FROM sections sec
				LEFT JOIN levels lvl ON sec.grade_level_id = lvl.level_id ";
				
		if($id == FALSE){
			$sql .= " WHERE sec.section_status = 'active' AND room_assigned = 'no' ORDER BY lvl.level_id ASC";
			$query = $this->db->query($sql);
		}else{
			$sql .= "WHERE sec.section_status = 'active' AND sec.id = ? ORDER BY lvl.level_id ASC";
			$query = $this->db->query($sql,array($id));
		}
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function get_all_sections_master()
	{
		$sql = "SELECT * FROM sections";
		$query = $this->db->query($sql);
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	

	/*
		query to count all grade level section for pagnation
		11/12/11/2012
	*/	
	public function count_grade_level_section()
	{
		return $this->db->where('section_status','active')->count_all($this->__table);
	}
	
	/*
		query to fetch all grade level section for pagnation
		11/12/11/2012
	*/	
	public function fetch_grade_level_section($limit, $start,$all = FALSE,$id = '')
	{
	
		if($all == FALSE)
		{
			$this->db->limit($limit, $start);
		}else{
			$this->db->where('lvl.level_id',$id);
		}
		
		$query = $this->db->select('sec.id,sec.section_name,sec.section_status,lvl.level_desc,concat(emp.first_name," ",emp.last_name) as advisers_name,(max_load - current_load) as remaining_slot,max_load',FALSE)
							  ->from('sections sec')
							  ->join('levels lvl','sec.grade_level_id=lvl.level_id','LEFT')
							  ->join('employees emp','sec.adviser_id=emp.emp_id','LEFT')
							  ->where('sec.section_status','active')
							  ->where('sec.year_id',$this->c_sy->id)
							  ->order_by('sec.grade_level_id','ASC')
							  ->get();
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
		
	}

	/*
		query to update section
		11/12/11/2012
	*/
	public function update_grade_level_section($input,$id)
	{
		if($this->verify_data($input,2) == FALSE)
		{
			$this->db->set($input)->where('id',$id)->update($this->__table);
			return $this->db->affected_rows() > 0 ? array('status'=>'true') : array('status'=>'false');			
		}else{
			return array('status'=>'exist');
		}
	}
	
	
	/*
		add_grade_level_section
		@param array
		adds grade level section to database
		12/11/2012
	*/
	public function add_grade_level_section($input)
	{	
		$find_duplicate = $this->db->where($input)->get($this->__table);
		if($find_duplicate->num_rows() > 0)
		{ 
			return array('status'=>'false','msg'=>'Duplicate Data Exists');
		}else
		{
			$input['year_id'] = $this->c_sy->id;//current year id
			$this->db->insert($this->__table,$input);
			$last_insert_id = $this->db->insert_id();
			if($this->db->affected_rows() > 0)
			{
				//insert subjects from curriculum to this section
				$sql = "INSERT INTO schedule (subject_id,section_id)
						SELECT c.subject_id,?
						FROM curriculum c
						WHERE c.level_id = ?
						AND c.ay_id = ? ";
				$this->db->query($sql,array($last_insert_id,$input['grade_level_id'],$this->c_sy->id));
				
				if($this->db->affected_rows() >= 1)
				{
					return array('status'=>'true','msg'=>'Section has been added.');
				}else{
					return array('status'=>'false','msg'=>'Unable to insert subjects from curriculum.');
				}
			}else{
				return array('status'=>'false','msg'=>'An error occured while inserting section.');
			}
		}
	}
	
	/*
		@param array
		add subject to schedule table 
		09/30/2013
	*/
	public function add_subject_to_section($input)
	{	
			$input['year_id'] = $this->c_sy->id;//current year id

				//insert subjects from curriculum to this section
				$sql = "INSERT INTO schedule (subject_id,section_id)
						SELECT c.subject_id,?
						FROM curriculum c
						WHERE c.level_id = ?
						AND c.ay_id = ? ";
				$this->db->query($sql,array($input['0'],$input['1'],$this->c_sy->id));
				
				if($this->db->affected_rows() >= 1)
				{
					return array('status'=>'true','msg'=>'Subject(s) has been added to the Section.');
				}else{
					return array('status'=>'false','msg'=>'Unable to insert Subject(s) from Curriculum.');
				}
			
	}
	
	/*
		query to update section_status to inactive
		11/12/11/2012
	*/
	public function destroy($id)
	{
	
		$sql = 'SELECT from ROOMS where section_id = ?';
		$query = $this->db->where('section_id',$id)->get('rooms');
		
		if($query->num_rows() >=1)
		{
			$this->db->where('section_id',$id)->delete('rooms');
			if($this->db->affected_rows() >=1)
			{
				$this->db->where('id',$id)->delete('sections');
				return $this->db->affected_rows() >=1 ? TRUE : FALSE;
			}
		}else{
			$this->db->where('id',$id)->delete('sections');
			return $this->db->affected_rows() >=1 ? TRUE : FALSE;
		
			$this->db->set('e_section_id',0)
					 ->where('e_section_id',$id)
					 ->where('e_ay_id',$this->c_sy->id)
					 ->where('e_archived',0)
					 ->where('verified',1)
					 ->update('enrollments');
					 
		}
	}
	
	/*
		query to update section_status to active
		11/12/11/2012
	*/	
	public function undestroy($id)
	{
		$data['section_status'] = 'active';
		$this->db->set($data)->where('id',$id)->update($this->__table);
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;		
	}	
	
	/* verify_data
	 * @param array
	 * @param int
	*  verifies if data entered by user is existing already in the database
	*  $strict level 1,2
	*  level 1 = if one field has existing data in database returns true else false
	*  level 2 = if all field has existing data in database returns true else false
	*  12/11/2012
	*/
	public function verify_data($data,$strict_level)
	{
			if($strict_level == 1)
			{
				$query = $this->db->or_where($data)->get($this->__table);
			}elseif($strict_level == 2){
				$query = $this->db->where($data)->get($this->__table);
			}
			return $query->num_rows() > 0 ? TRUE : FALSE;
	}
	
	
	public function get_section($section_id){
		$q = $this->db->select('id,section_name,grade_level_id')->where('id', $section_id)->get('sections');
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	public function get_section_by_grade_level($level_id)
	{
		$query = "SELECT id,section_name FROM sections 
				  WHERE grade_level_id = ? 
				  AND max_load != current_load 
				  AND max_load != 0
				  AND year_id = ?";
		$q = $this->db->query($query,array($level_id,$this->c_sy->id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function get_section_by_grade_level_array($level_id)
	{
		$query = "SELECT id,section_name 
				  FROM sections 
				  WHERE grade_level_id = ? 
				  AND max_load != current_load 
				  AND max_load != 0
				  AND year_id = ?";
			$q = $this->db->query($query,array($level_id,$this->c_sy->id));
			
			if($q->num_rows() >= 1)
			{
				$sections = $q->result();
				$opt[''] ='--- Select Section ---';
				foreach($sections as $section){
					$opt[$section->id] = $section->section_name;
				}
			}else{
				$opt[''] = 'Sections is not available';
			}
			return  $opt;
	}
	
	public function set_sections_room_assigned($section_id)
	{
		$sql = 'update sections set room_assigned = ? where id = ?'; 
		$this->db->query($sql,array('yes',$section_id));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	public function unset_sections_room_assigned($section_id)
	{
		$sql = 'update sections set room_assigned = ? where id = ?'; 
		$this->db->query($sql,array('no',$section_id));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function set_sections_level_assigned($section_id)
	{
		foreach($section_id as $key => $data_to_update)
		{
			$data[$key]['id'] = $data_to_update;
			$data[$key]['level_assigned'] ='yes';
		}
		$this->db->update_batch($this->__table, $data,'id'); 
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function unset_sections_level_assigned($section_id)
	{
		foreach($section_id as $key => $data_to_update)
		{
			$data[$key]['id'] = $data_to_update;
			$data[$key]['level_assigned'] ='no';
		}
		$this->db->update_batch($this->__table, $data,'id'); 

		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	
	/* when assigning sections to room this will update the section room_assigned status to yes */
	public function update_section_room_assigned_status($id)
	{
		$sql = "UPDATE sections set room_assigned = 'yes' WHERE id = ?";
		$this->db->query($sql,array($id));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function update_section_load($id,$mode = FALSE)
	{
		if(strtolower($mode) == 'add'){
			$sql = "UPDATE sections set current_load = (current_load +1) where id = ? AND max_load != 0";
			
		}elseif(strtolower($mode) == 'sub')
		{
			$sql = "UPDATE sections set current_load = (current_load -1) where id = ? AND max_load != 0 AND current_load != 0";
		}else{
			return FALSE;
		}
		
		$this->db->query($sql,array($id));
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function by_grade_level($level_id)
	{
		$query = "SELECT id,section_name FROM sections 
				  WHERE grade_level_id = ?";
		$q = $this->db->query($query,array($level_id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function get_specific_coloumn($data)
	{	
		$query = $this->db->select($data)->get('sections');
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_student_section($enrollment_id)
	{
		$sql = "SELECT level_code, 
						section_name
				FROM enrollments
				JOIN sections ON enrollments.e_section_id = sections.id
				JOIN levels ON sections.grade_level_id = levels.level_id
				WHERE enrollments.e_id = ?
				AND sections.year_id = ?";
		$query = $this->db->query($sql, array($enrollment_id,$this->c_sy->id));
		return $query->num_rows() > 0 ? $query->row() : FALSE;
	}
	
	/*
		get section data through subject
	*/
	public function get_section_id_by_subject($subject_id)
	{
		$sql = "SELECT sections.id FROM sections
				JOIN schedule ON sections.id = schedule.section_id
				JOIN subjects ON schedule.subject_id = subjects.subj_id
				WHERE subjects.subj_id = ?";
		$query = $this->db->query($sql, array($subject_id));
		return $query->num_rows() > 0 ? $query->row() : FALSE;
	}
	
	public function get_all_students_in_section($section_id)
	{
		$sql = "SELECT 
						sections.section_name,
						concat(profiles.lastname,' ,',profiles.firstname,' ',profiles.middlename) as fullname,
						profiles.gender,
						enrollments.e_student_id,
						DATE_FORMAT(enrollments.e_created_at,'%M %c,%Y') as regdate,
						enrollments.e_id as enrollment_id
				FROM enrollments
				LEFT JOIN sections ON enrollments.e_section_id = sections.id
				LEFT JOIN profiles ON profiles.profile_id = enrollments.e_profile_id
				WHERE enrollments.e_section_id = ?
				AND enrollments.e_ay_id = ?
				AND sections.year_id = ?
				";
		$query = $this->db->query($sql, array($section_id,$this->c_sy->id,$this->c_sy->id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_section_masterlist()
	{
		$sql ="SELECT sec.id, sec.section_name, level_desc
			   FROM sections sec
			   LEFT JOIN levels lvl ON lvl.level_id = sec.grade_level_id
			   WHERE sec.year_id = ?";
	
		$result = $this->db->query($sql,array($this->c_sy->id));
		
		return $result->num_rows() >=1 ? $result->result() : FALSE;
	}
	
	public function get_section_masterlist_complete()
	{
		$ci =& get_instance();
		$ci->load->model(array('M_levels'));
		
		$levels = $ci->M_levels->get_all_levels();
		
		
		
		
		foreach($levels as $lvlk => $lvlv)
		{
			$sections = $this->get_section_by_grade_level($lvlv->level_id);
			$processed[$lvlk]['level']= $lvlv->level_desc;
			if($sections !== FALSE)
			{
				foreach($sections as $seck => $secv)
				{
					$processed[$lvlk]['data'][$seck]['section_name'] = $secv->section_name;
					$processed[$lvlk]['data'][$seck]['students'] = $this->get_all_students_in_section($secv->id);
				}
			}else{
			
			}
		}
	
		return $processed;
	}
}
?>
